package com.example.sqlutils;

import lombok.Data;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.logging.Logger;

@Data
public class SQlLUtil<T extends ResultSet, E> {

    static final Logger logger = Logger.getLogger(String.valueOf(SQlLUtil.class));

    // JDBC driver name and database URL
    static final String DB_URL = "jdbc:mysql://139.224.237.182:32700/dev-longwang-v2?useSSL=false";
    static final String USER = "root";
    static final String PASS = "root";

    static final int COUNT = 10000;

    final Connection conn;


    public interface Function<T  extends ResultSet,E>{

        E apply(ResultSet resultSet) throws SQLException;
    }

    public interface Consumption<E>{
        Object apply(E e);
    }

    public interface ConsumptionTWO<T,R>{
        void apply(T t, R r);
    }

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    SQlLUtil() throws ClassNotFoundException, SQLException {
        conn = DriverManager.getConnection(DB_URL,USER,PASS);
    }

    SQlLUtil(String DB_URL) throws ClassNotFoundException, SQLException {
        conn = DriverManager.getConnection(DB_URL,USER,PASS);
    }

    public Integer updateSQL(String sql,List<E> list,ConsumptionTWO<PreparedStatement,List<E>> consumptionTWO ) throws SQLException {
//        logger.info(sql);
        int rs = 0;
        try {
            conn.setAutoCommit(false);
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            consumptionTWO.apply(preparedStatement, list);
            int[] rss = preparedStatement.executeBatch();
            for (int r: rss) {
                rs += r;
            }
            preparedStatement.close();
            conn.commit();
        } catch (SQLException sqlException){
            conn.close();
        }
        return rs;
    }


    public E runSQL(String sql, Function<T,E> function) throws SQLException {
//        logger.info(sql);
        E e = null;
        try {
            PreparedStatement preparedStatement = conn.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            e  = function.apply(rs);
            rs.close();
            preparedStatement.close();
        }catch (SQLException sqlException){
            sqlException.printStackTrace();
            conn.close();
        }
        return e;
    }

    public List<E> runSQLList (String sql, Function<T,List<E>> function) throws SQLException {
//        logger.info(sql);
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        preparedStatement.setFetchSize(COUNT);
        ResultSet rs =  preparedStatement.executeQuery();
        rs.setFetchSize(COUNT);
        List<E> e  = function.apply(rs);
        rs.close();
        preparedStatement.close();
        return e;
    }

    public String join(Collection<E> list, Consumption<E> consumption){
        StringBuffer stringBuffer = new StringBuffer("(");
        List<Object> temp = new ArrayList<>();
        list.forEach(
                item->{
                    temp.add(consumption.apply(item));
                }
        );
        temp.stream().distinct().forEach(
                item->{
                    stringBuffer.append(item);
                    stringBuffer.append(",");
                }
        );
        stringBuffer.delete(stringBuffer.length()- 1,stringBuffer.length());
        stringBuffer.append(")");
        return stringBuffer.toString();

    }

    public void close() throws SQLException {
        conn.close();
    }
}
